package com.pdg.mcplugin.ranger.dataproviders;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.bukkit.entity.Player;

import com.pdg.mcplugin.common.baseclasses.DataProviderBase;
import com.pdg.mcplugin.common.baseclasses.TableProviderBase;
import com.pdg.mcplugin.ranger.Ranger;

public class PlaceNameTableProvider extends TableProviderBase<Ranger,DataProvider,PlaceNameKey,PlaceNameLocation> {

	private static final String TABLE_NAME = "place_names";
	private static final String DB_RETRIEVE_PLACE_NAMES = "SELECT `"+
			PlaceNameKey.FIELD_PLACE_NAME+"` FROM `"+
			TABLE_NAME+"` WHERE `"+
			PlaceNameKey.FIELD_PLAYER+"`='%s' AND `"+
			PlaceNameKey.FIELD_WORLD+"`='%s' ORDER BY `"+
			PlaceNameKey.FIELD_PLACE_NAME+"`;";
	private static final String DB_RETRIEVE_FILTERED_PLACE_NAMES = "SELECT `"+
			PlaceNameKey.FIELD_PLACE_NAME+"` FROM `"+
			TABLE_NAME+"` WHERE `"+
			PlaceNameKey.FIELD_PLAYER+"`='%s' AND `"+
			PlaceNameKey.FIELD_WORLD+"`='%s' AND `"+
			PlaceNameKey.FIELD_PLACE_NAME+"` LIKE '%s%%' ORDER BY `"+
			PlaceNameKey.FIELD_PLACE_NAME+"`;";
	private static final String DB_RETRIEVE_PLACE_NAMES_FOR_PLAYER = "SELECT `"+
			PlaceNameKey.FIELD_PLACE_NAME+"`, `"+
			PlaceNameLocation.FIELD_X+"`, `"+
			PlaceNameLocation.FIELD_Y+"`, `"+
			PlaceNameLocation.FIELD_Z+"` FROM `"+
			TABLE_NAME+"` WHERE `"+
			PlaceNameKey.FIELD_PLAYER+"`='%s' AND `"+
			PlaceNameKey.FIELD_WORLD+"`='%s' ORDER BY `"+
			PlaceNameKey.FIELD_PLACE_NAME+"`;";
	private static final String ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES = "Could not retrieve place names!";
	private static final String ERROR_COULD_NOT_RETRIEVE_FILTERED_PLACE_NAMES = "Could not retrieve filtered place names!";
	private static final String ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES_FOR_PLAYER = "Could not retrieve place names for player!";
	
	public PlaceNameTableProvider(DataProvider theDataProvider) {
		super(theDataProvider, TABLE_NAME, new PlaceNameKey("", "", ""), new PlaceNameLocation(0, 0, 0));
	}

	public List<String> retrievePlaceNamesForPlayer(Player thePlayer) {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = String.format(DB_RETRIEVE_PLACE_NAMES,
					DataProviderBase.sqlSafeString(thePlayer.getName()),
					DataProviderBase.sqlSafeString(thePlayer.getWorld().getName()));
			ResultSet result = statement.executeQuery(theQuery);
			List<String> theResult = new ArrayList<String>();
			while(result.next()){
				theResult.add(result.getString(PlaceNameKey.FIELD_PLACE_NAME));
			}
			return theResult;
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES));
			e.printStackTrace();
		}
		return null;
	}

	public List<String> retrieveFilteredPlaceNamesForPlayer(Player thePlayer,
			String theFilter) {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = String.format(DB_RETRIEVE_FILTERED_PLACE_NAMES,
					DataProviderBase.sqlSafeString(thePlayer.getName()),
					DataProviderBase.sqlSafeString(thePlayer.getWorld().getName()),
					DataProviderBase.sqlSafeString(theFilter));
			ResultSet result = statement.executeQuery(theQuery);
			List<String> theResult = new ArrayList<String>();
			while(result.next()){
				theResult.add(result.getString(PlaceNameKey.FIELD_PLACE_NAME));
			}
			return theResult;
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(ERROR_COULD_NOT_RETRIEVE_FILTERED_PLACE_NAMES));
			e.printStackTrace();
		}
		return null;
	}

	public Map<String, PlaceNameLocation> getPlaceNamesForPlayer(
			Player thePlayer) {
		try{
			//create a statement to check for inventory table
			Statement statement = getDataProvider().getConnection().createStatement();
			String theQuery = String.format(DB_RETRIEVE_PLACE_NAMES_FOR_PLAYER,
					DataProviderBase.sqlSafeString(thePlayer.getName()),
					DataProviderBase.sqlSafeString(thePlayer.getWorld().getName()));
			ResultSet result = statement.executeQuery(theQuery);
			Map<String,PlaceNameLocation> theResult = new HashMap<String,PlaceNameLocation>();
			while(result.next()){
				PlaceNameLocation thePlaceNameLocation = new PlaceNameLocation(
						result.getDouble(PlaceNameLocation.FIELD_X), 
						result.getDouble(PlaceNameLocation.FIELD_Y), 
						result.getDouble(PlaceNameLocation.FIELD_Z));
				String thePlaceName = result.getString(PlaceNameKey.FIELD_PLACE_NAME);
				theResult.put(thePlaceName,thePlaceNameLocation);
			}
			return theResult;
		}catch(Exception e){
			//an error has occurred
			getLogger().severe(getPlugin().formatMessage(ERROR_COULD_NOT_RETRIEVE_PLACE_NAMES_FOR_PLAYER));
			e.printStackTrace();
		}
		return null;
	}
	
	
}
